from datetime import datetime
import os
import pymysql

class MysqlBackupTool:
    # 数据库连接信息
    #sql.host = 'localhost'
    #sql.user = 'root'
    #sql.password = '123456'
    #sql.db = 'thinkcmf'
    changeList = [
        ["'","\\'"],
    ]

    #初始化数据库连接
    def initmysql(self,sql):
        self.sql = sql
        
        # 连接到数据库
        self.connection = pymysql.connect(host=sql['host'],user=sql['user'],password=sql['password'],db=sql['db'])

        current_time = datetime.now()
        current_time = current_time.strftime("%Y-%m-%d_%H:%M:%S")
        folder = f'data/{sql["db"]}_{current_time}'
        os.makedirs(folder, exist_ok=True)
        self.folder = folder

    #关闭数据库连接s
    def closeMysql(self):
        self.connection.close()

        
    #备份指定表
    def backupTable(self,table,backup_file):
        try:
            with open(backup_file, 'w') as f:
                # 创建游标对   象
                cursor = self.connection.cursor()

                # 执行 SQL 查询
                cursor.execute(f'SHOW CREATE TABLE {table}')
        
                # 获取查询结果
                create_table = cursor.fetchone()

                # 打印 create table 语句
                f.write(f'DROP TABLE {table};\n')
                f.write(f'{create_table[1]};\n')

                # 关闭游标对象
                cursor.close()

    
                # 创建一个游标对象
                with self.connection.cursor() as cursor:
    
                    # 选择所有记录
                    sql = "SELECT * FROM {}".format(table)
                    cursor.execute(sql)

                    # 获取所有列的名称
                    columns = [i[0] for i in cursor.description]

            
                    # 写入列名称到备份文件

                    t = "INSERT INTO {} (`{}`) VALUES ".format(table, "`, `".join(columns))

                    # 遍历记录并写入到备份文件
                    for row in cursor:
                        f.write("{} ({});\n".format(t,", ".join(["'%s'" % self.txtReplace(i) for i in row])))
                        pass
                print(f"[ {table} ]  success...")

        except Exception as e:
            print(f"Error: unable to backup table {table} => ", e)

    # 替换文本特殊符号
    def txtReplace(self,val):
        if not isinstance(val,str):
            return val
        
        for i in self.changeList:
            val = val.replace(i[0],i[1])
        return val
    
    # 获取数据库下的所有表
    def getTablesName(self):
        # 创建游标对象
        cursor = self.connection.cursor()

        # 执行 SQL 查询
        cursor.execute('SHOW TABLES')

        # 获取查询结果
        tables = cursor.fetchall()

        # 关闭游标对象
        cursor.close()
        
        return tables   

    # 备份数据库中所有表
    def BackAllTable(self):
        TablesName = self.getTablesName()
        
        for i in TablesName:
            tableName = i[0]
            self.backupTable(tableName,f'{self.folder}/{tableName}.sql')





